package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import models.ExercicioTreino;
import models.Treino;

public class DAOTreino {

	public Set<Treino> select() throws Exception {
		Set<Treino> objs = new HashSet<Treino>();
		
		try {

			String query = "SELECT t.idTreino, t.idAluno, t.dataInicial, t.dataFinal, t.idProfessor, a.nome, p.nome " +
					"FROM Treino t, Usuario a, Usuario p " +
					"WHERE p.idUsuario = t.idProfessor " +
					"	AND a.idUsuario = t.idAluno";

			Statement statement = DAOConnection.getConnection().createStatement();

			ResultSet resultSet = statement.executeQuery(query);

			while(resultSet.next()) {
				Treino obj = new Treino();
				obj.idTreino = resultSet.getInt(1);
				obj.idAluno= resultSet.getInt(2);
				obj.dataInicial = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				obj.dataFinal = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				obj.idProfessor = resultSet.getInt(5);
				obj.aluno.nome = resultSet.getString(6);
				obj.professor.nome = resultSet.getString(7);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return objs; 
	}
	
	public Treino select(Treino t) throws Exception {
		Treino treino = null;
		
		try {

			String query = "SELECT idTreino, idAluno, dataInicial, dataFinal, idProfessor FROM Treino WHERE idAluno = ? AND idProfessor = ? ORDER BY idTreino DESC";

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(query);
			ps.setInt(1, t.idAluno);
			ps.setInt(2, t.idProfessor);
			
			ResultSet resultSet = ps.executeQuery();

			if(resultSet.next()) {
				treino = new Treino();
				treino.idTreino = resultSet.getInt(1);
				treino.idAluno= resultSet.getInt(2);
				treino.dataInicial = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				treino.dataFinal = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				treino.idProfessor = resultSet.getInt(5);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return treino; 
	}
	
	
	//retorna conjunto de treinos do aluno
	public Set<Treino> selectTreinoAluno(int idAluno) throws Exception {
		Set<Treino> objs = new HashSet<Treino>();
		
		try {

			String query = "SELECT t.idTreino, t.idAluno, t.dataInicial, t.dataFinal, t.idProfessor, a.nome, p.nome " +
					"FROM Treino t, Usuario a, Usuario p " +
					"WHERE p.idUsuario = t.idProfessor " +
					"	AND a.idUsuario = t.idAluno " +
					"	AND t.idAluno = ? " +
					"ORDER BY dataInicial";

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(query);
			ps.setInt(1, idAluno);
			
			ResultSet resultSet = ps.executeQuery();

			while(resultSet.next()) {
				Treino obj = new Treino();
				obj.idTreino = resultSet.getInt(1);
				obj.idAluno= resultSet.getInt(2);
				obj.dataInicial = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				obj.dataFinal = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				obj.idProfessor = resultSet.getInt(5);
				obj.aluno.nome = resultSet.getString(6);
				obj.professor.nome = resultSet.getString(7);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return objs; 
	}
	
	//retorna conjunto de treinos do professor
	public Set<Treino> selectTreinoProfessor(int idProfessor) throws Exception {
		Set<Treino> objs = new HashSet<Treino>();
		
		try {

			String query = "SELECT t.idTreino, t.idAluno, t.dataInicial, t.dataFinal, t.idProfessor, a.nome, p.nome " +
					"FROM Treino t, Usuario a, Usuario p " +
					"WHERE p.idUsuario = t.idProfessor " +
					"	AND a.idUsuario = t.idAluno " +
					"	AND t.idProfessor = ? " +
					"ORDER BY dataInicial";

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(query);
			ps.setInt(1, idProfessor);
			
			ResultSet resultSet = ps.executeQuery();

			while(resultSet.next()) {
				Treino obj = new Treino();
				obj.idTreino = resultSet.getInt(1);
				obj.idAluno= resultSet.getInt(2);
				obj.dataInicial = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				obj.dataFinal = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				obj.idProfessor = resultSet.getInt(5);
				obj.aluno.nome = resultSet.getString(6);
				obj.professor.nome = resultSet.getString(7);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return objs; 
	}


	public Treino select(int id) throws Exception {
		Treino obj = new Treino();
		try	{

			String query = "SELECT t.idTreino, t.idAluno, t.dataInicial, t.dataFinal, t.idProfessor, a.nome, p.nome " +
					"FROM Treino t, Usuario a, Usuario p " +
					"WHERE p.idUsuario = t.idProfessor " +
					"	AND a.idUsuario = t.idAluno " +
					"	AND t.idTreino = ? " +
					"ORDER BY dataInicial";

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(query);
			ps.setInt(1, id);
			
			ResultSet resultSet = ps.executeQuery();

			while(resultSet.next()) {
				obj.idTreino = resultSet.getInt(1);
				obj.idAluno= resultSet.getInt(2);
				obj.dataInicial = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				obj.dataFinal = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				obj.idProfessor = resultSet.getInt(5);
				obj.aluno.nome = resultSet.getString(6);
				obj.professor.nome = resultSet.getString(7);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return obj; 
	}

	public void insert(Treino t) throws Exception {
		try {
			String insert = "INSERT INTO Treino (idAluno, dataInicial, dataFinal, idProfessor) VALUES(?, ?, ?, ?)";
			
			System.out.println(t);

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(insert);
			ps.setInt(1, t.idAluno);

			//converte string com data no formato pt-BR para Date 
			Date dataTmp = DAOConnection.string2DateHTML(t.dataInicial);
			//converte dataTmp para string no formato data do sql
			ps.setString(2, DAOConnection.date2StringSQL(dataTmp));
			//ps.setDate(2, (java.sql.Date)dataTmp);

			//converte string com data no formato pt-BR para Date
			dataTmp = DAOConnection.string2DateHTML(t.dataFinal);
			//converte dataTmp para string no formato data do sql
			ps.setString(3, DAOConnection.date2StringSQL(dataTmp));
			//ps.setDate(3, (java.sql.Date)dataTmp);
			
			ps.setInt(4, t.idProfessor);

			System.out.println(ps.toString());
			ps.executeUpdate();
			
			Treino treino = select(t);
			
			DAOExercicioTreino daoEt = new DAOExercicioTreino();
			for (ExercicioTreino exercicioT : t.exercicio) {
				exercicioT.idTreino = treino.idTreino;
				daoEt.insert(exercicioT);
			}
			
		} catch(SQLException e) {	
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
	}

	public void update(Treino t) throws Exception {
		try {

			String update = "UPDATE Treino SET idAluno = ?, dataInicial = ?, dataFinal = ?, idProfessor = ? WHERE idTreino = ?";

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(update);
			ps.setInt(1, t.idAluno);

			//converte string com data no formato pt-BR para Date 
			Date dataTmp = DAOConnection.string2DatePTBR(t.dataInicial);
			//converte dataTmp para string no formato data do sql
			ps.setString(2, DAOConnection.date2StringSQL(dataTmp));

			//converte string com data no formato pt-BR para Date
			dataTmp = DAOConnection.string2DatePTBR(t.dataFinal);
			//converte dataTmp para string no formato data do sql
			ps.setString(3, DAOConnection.date2StringSQL(dataTmp));
			
			ps.setInt(4, t.idProfessor);
			ps.setInt(5, t.idTreino);
			
			ps.executeUpdate();
		} catch(SQLException e) {
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}

	}

	public void delete(Treino t) throws Exception {
		try {

			DAOExercicioTreino dao = new DAOExercicioTreino();
			dao.delete(t);
			
			String delete = "DELETE FROM Treino WHERE idTreino = ?";

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(delete);
			ps.setInt(1, t.idTreino);

			ps.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
	}

}